{
  "cells": [
    {
      "metadata": {
        "toc": true
      },
      "cell_type": "markdown",
      "source": "<h1>索引<span class=\"tocSkip\"></span></h1>\n<div class=\"toc\"><ul class=\"toc-item\"><li><span><a href=\"#写在开头的话\" data-toc-modified-id=\"写在开头的话-1\">写在开头的话</a></span><ul class=\"toc-item\"><li><ul class=\"toc-item\"><li><span><a href=\"#问题1.-我用-openpyxl-写-Excel，Excel-居然说有错误，怎么办？\" data-toc-modified-id=\"问题1.-我用-openpyxl-写-Excel，Excel-居然说有错误，怎么办？-1.0.1\">问题1. 我用 openpyxl 写 Excel，Excel 居然说有错误，怎么办？</a></span></li></ul></li></ul></li></ul></div>"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "## 写在开头的话\n\n本人写的文章只是个人工作经验的免费分享，不代表本人供职公司的观点，不承担由此带来的任何责任。\n\n我会把Python编程过程中遇到的问题，学到的东西，通过jupyter notebook形式发表出来。大家可以边看边学边动手。\n\n这样做的对你好处是：\n\n - 只有动手，你才能学会\n - 只有动手，你才能学会\n - 只有动手，你才能学会\n"
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "#### 问题1. 我用 openpyxl 写 Excel，Excel 居然说有错误，怎么办？\n\n问题描述看下面的代码："
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "from openpyxl import Workbook\n\nwb = Workbook()\nsheet = wb.active\nline=\"============\"\nsheet.title=\"error1\"\n# 添加一个单元格到位置1,1，内容设置为line\ncell = sheet.cell(1, 1, value=line)\nwb.save(\"error1.xlsx\")",
      "execution_count": 32,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": "如果把excel文件[eroor1.xlsx](error1.xlsx)打开，我们会看到下面这个错误。\n\n![Error](pics/alert.png)\n\n怎么回事呢？\n\n如果我们选择 yes, 系统会进一步询问你要不要看错误？\n\n你如果选择\"view\", 你就会看到如下内容：\n\n```xml\n<?xml version=\"1.0\" encoding=\"UTF-8\" standalone=\"yes\"?>\n<recoveryLog xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\"><logFileName>Repair Result to error10.xml</logFileName><summary>Errors were detected in file '/Downloads/error1.xlsx'</summary><removedRecords summary=\"Following is a list of removed records:\"><removedRecord>Removed Records: Formula from /xl/worksheets/sheet1.xml part</removedRecord></removedRecords></recoveryLog>\n```\n\nexcel认为里面有记录不对，所以excel日志说他把错误记录删除删除.\n在修复好的excel中，我们可以看到位置（1，1） 一片空白。也就是Excel认为这个单元有问题。 那么好吧，我们来看一下问题在哪里吧，这个单元的内容居然是”=====“。  嗯，这就是问题所在了，Excel认为这是一个错误的单元格。我们需要设置它的类型为Text就没事了。"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "# openpyxl 2.5.8 写法, 如果是3.xx 版本，应该是\n# from openpyxl.cell.cell import TYPE_STRING\nfrom openpyxl.cell.cell import Cell\nwb = Workbook()\nsheet = wb.active\nline=\"============\"\nsheet.title=\"correct\"\n# 添加一个单元格到位置1,1，内容设置为line\ncell = sheet.cell(1, 1, value=line)\ncell.data_type = Cell.TYPE_STRING\nwb.save(\"correct.xlsx\")\nwb.close()",
      "execution_count": 33,
      "outputs": []
    },
    {
      "metadata": {},
      "cell_type": "markdown",
      "source": " 这样就对了。<a href=\"correct.xlsx\" download>correct.xlsx</a>"
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "#检查模块版本号\nimport openpyxl\nopenpyxl.__version__",
      "execution_count": 34,
      "outputs": [
        {
          "output_type": "execute_result",
          "execution_count": 34,
          "data": {
            "text/plain": "'2.5.8'"
          },
          "metadata": {}
        }
      ]
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "#clean up\nimport os\nos.remove(\"error1.xlsx\")\nos.remove(\"correct.xlsx\")",
      "execution_count": 35,
      "outputs": []
    },
    {
      "metadata": {
        "trusted": true
      },
      "cell_type": "code",
      "source": "",
      "execution_count": null,
      "outputs": []
    }
  ],
  "metadata": {
    "kernelspec": {
      "name": "python36",
      "display_name": "Python 3.6",
      "language": "python"
    },
    "toc": {
      "base_numbering": 1,
      "nav_menu": {},
      "number_sections": false,
      "sideBar": true,
      "skip_h1_title": true,
      "title_cell": "索引",
      "title_sidebar": "索引",
      "toc_cell": true,
      "toc_position": {},
      "toc_section_display": true,
      "toc_window_display": true
    },
    "language_info": {
      "mimetype": "text/x-python",
      "nbconvert_exporter": "python",
      "name": "python",
      "pygments_lexer": "ipython3",
      "version": "3.6.6",
      "file_extension": ".py",
      "codemirror_mode": {
        "version": 3,
        "name": "ipython"
      }
    }
  },
  "nbformat": 4,
  "nbformat_minor": 2
}